package com.cogent.dao.orm.impl;

import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.cogent.common.exception.SystemRuntimeException;
import com.cogent.common.io.FileUtil;
import com.cogent.common.util.MapUtil;
import com.cogent.common.util.SqlDateUtil;
import com.cogent.common.util.StringUtil;
import com.cogent.dao.domain.ColumnDomain;
import com.cogent.dao.domain.Pagination;
import com.cogent.dao.domain.ParamDomain;
import com.cogent.dao.domain.TableInfoDomain;
import com.cogent.dao.orm.AbstractDBOperator;
import com.cogent.dao.orm.IDGen;

public class MySqlDBOperatorImpl extends AbstractDBOperator {
	private final String TABLE_INFO_QUERY_SQL = "select t.column_name as colname,t.data_type as coltype, t.numeric_scale as scale from information_schema.columns t where t.table_name=? and t.table_schema=? order by t.ordinal_position asc";

	//日志对象
	private Logger _log = Logger.getLogger(MySqlDBOperatorImpl.class); 
	//全局缓存对象
	private static Map<String, Object> CACHED_TABLE_MAP = null;
		
	//全局字段对应信息MAP
	private static Map<String, String> CACHED_COLUMN_MAPED_MAP = null;
	
	public MySqlDBOperatorImpl() {
		CACHED_TABLE_MAP = new HashMap<String, Object>();
		CACHED_COLUMN_MAPED_MAP = new HashMap<String, String>();
		//1为整形类型
		CACHED_COLUMN_MAPED_MAP.put("integer", "1");
		CACHED_COLUMN_MAPED_MAP.put("float", "1");
		CACHED_COLUMN_MAPED_MAP.put("real", "1");
		CACHED_COLUMN_MAPED_MAP.put("int", "1");
		CACHED_COLUMN_MAPED_MAP.put("bit", "1");
		//2为浮点类型
		CACHED_COLUMN_MAPED_MAP.put("number", "2");
		CACHED_COLUMN_MAPED_MAP.put("decimal", "2");
		CACHED_COLUMN_MAPED_MAP.put("double", "2");
		//3为日期类型
		CACHED_COLUMN_MAPED_MAP.put("datetime", "3");
		CACHED_COLUMN_MAPED_MAP.put("timestamp", "3");
		//4为字符类型
		CACHED_COLUMN_MAPED_MAP.put("char", "4");
		CACHED_COLUMN_MAPED_MAP.put("varchar", "4"); 
		CACHED_COLUMN_MAPED_MAP.put("long", "4");
		CACHED_COLUMN_MAPED_MAP.put("clob", "4");
		CACHED_COLUMN_MAPED_MAP.put("text", "4"); 
		CACHED_COLUMN_MAPED_MAP.put("nclob", "4");
		//5为BLOB类型
		CACHED_COLUMN_MAPED_MAP.put("blob", "5");
		CACHED_COLUMN_MAPED_MAP.put("num_scale", "9");
	}
	
	public Map getDBFieldMappedType() {
		return CACHED_COLUMN_MAPED_MAP;
	}
	
	public TableInfoDomain getColumnInfo(String tableName) {
		tableName = tableName.toUpperCase();
		String key = "TABLE_COLUNM_" + tableName+getUserName().toUpperCase();
		TableInfoDomain tableInfoDomain = null;
		if(CACHED_TABLE_MAP.containsKey(key)) {
			tableInfoDomain = (TableInfoDomain)CACHED_TABLE_MAP.get(key);
		} else {
			PreparedStatement stat = null;
			ResultSet rs = null;
			boolean isopenflag = false;
			try {
				if(_con == null) {
					openDB();
					isopenflag = true;
				}
				stat = _con.prepareStatement(TABLE_INFO_QUERY_SQL);
				stat.setString(2, this.getUserName());
				stat.setString(1, tableName);
				rs = stat.executeQuery();
				tableInfoDomain = new TableInfoDomain();
				List<ColumnDomain> columnList = new ArrayList<ColumnDomain>();
				Map <String, String> columnkeymap = new HashMap();
				while(rs.next()) {
					ColumnDomain columnDomain= new ColumnDomain();
					String columnName = rs.getString("colname").toLowerCase();
					String columnType = rs.getString("coltype").toLowerCase();
					int scale = StringUtil.toInt(rs.getString("scale"));
					if(scale > 0)
						columnType = "num_scale";
					if(columnName.equals("id")) {
						tableInfoDomain.setId("id");
						if(MapUtil.getIntValue(CACHED_COLUMN_MAPED_MAP, columnType) < 3)
							tableInfoDomain.setIdtype(IDGen.MAX_ID_GEN);
						else
							tableInfoDomain.setIdtype(IDGen.UUID_GEN);
					}
					if((tableInfoDomain.getId() == null) && (columnName.equals("uuid"))) {
						tableInfoDomain.setId("uuid");
						tableInfoDomain.setIdtype(IDGen.UUID_GEN);
					}
					columnDomain.setColumnName(columnName);
					columnDomain.setColumnType(columnType);
					columnList.add(columnDomain);
					columnkeymap.put(columnName, columnType);
				}
				tableInfoDomain.setColumnList(columnList);
				tableInfoDomain.setColumnkeymap(columnkeymap);
				CACHED_TABLE_MAP.put(key, tableInfoDomain);
			} catch (SQLException e) {
				e.printStackTrace();
				_log.error("在获取表"+ tableName + "时发生错误，错误信息为：" + e.getMessage());
				throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
			}
			finally{
				closeStat(rs, stat);
				if(isopenflag)
					closeDB();
			}
		}
		
		return tableInfoDomain;
	}
	
	public Pagination queryPagination(String sql, List<ParamDomain> queryList, String order, int page, int pageSize) {
		if(order.length() < 1)
			order = "";
		sql = sql.toLowerCase();
		Pagination pagination = new Pagination();
		if(isPageCount()) {
			String countSql = getCountSql(sql + order);		
			HashMap curMap = getByID(countSql, queryList);
			pagination.setTotal(MapUtil.getIntValue(curMap, "sumnum"));
		}
		pagination.setPageSize(pageSize);
		pagination.setCpage(page);	
		String querySql = getPagedSql(sql, order, queryList, page, pageSize);
		pagination.setList(query(querySql, queryList));			
		
		return pagination;
	}
	
	public Pagination queryPagination(String sql, String order, int page, int pageSize) {
		if(order.length() < 1)
			order = "";
		sql = sql.toLowerCase();
		Pagination pagination = new Pagination();
		if(isPageCount()) {
			String countSql = getCountSql(sql);		
			HashMap curMap = getOneRow(countSql);
			pagination.setTotal(MapUtil.getIntValue(curMap, "sumnum"));	
		}
		pagination.setPageSize(pageSize);
		pagination.setCpage(page);	
		List<ParamDomain> queryList = new ArrayList<ParamDomain>();
		String querySql = getPagedSql(sql, order, queryList, page, pageSize);
		pagination.setList(query(querySql, queryList));		
		
		return pagination;
	}
	
	protected void bingParam(PreparedStatement stat, List<ParamDomain> paramList){
		Iterator<ParamDomain> it = paramList.iterator();
		int i = 1;
		try {
			while(it.hasNext()) {
				ParamDomain paramDomain = (ParamDomain)it.next();
				String value = CACHED_COLUMN_MAPED_MAP.get(paramDomain.getColumntype()).toString();
				if(value.equals("1")) {
					if(paramDomain.getColumnvalue() != null &&(paramDomain.getColumnvalue().toString().length() > 0)) {
						String fieldValue = (String)paramDomain.getColumnvalue();
						stat.setLong(i, Long.parseLong(fieldValue));						
					}else
						stat.setNull(i, Types.INTEGER);
				}else if((value.equals("2")) || (value.equals("9"))) {
					if(paramDomain.getColumnvalue() != null && (paramDomain.getColumnvalue().toString().length() > 0)) {
						String fieldValue = (String)paramDomain.getColumnvalue();
						stat.setDouble(i, Double.parseDouble(fieldValue));						
					}else
						stat.setNull(i, Types.DOUBLE);
				}else if(value.equals("3")) {
					if((paramDomain.getColumnvalue() != null) && (paramDomain.getColumnvalue().toString().length() > 0)) {
						String fieldValue = (String)paramDomain.getColumnvalue();
						stat.setTimestamp(i, SqlDateUtil.toSqlDate(fieldValue));			
					}else
						stat.setNull(i, Types.DATE);
				}else if(value.equals("4")) {
					if(paramDomain.getColumnvalue() != null) {
						String fieldValue = (String)paramDomain.getColumnvalue();
						stat.setString(i, fieldValue);						
					}else
						stat.setNull(i, Types.VARCHAR);
				}else if(value.equals("5")) {
					if(paramDomain.getColumnvalue() != null) {
						byte[] fieldValue = (byte[])paramDomain.getColumnvalue();
						if(fieldValue.length > 0) {
							InputStream is = FileUtil.byteToStream(fieldValue);
							stat.setBinaryStream(i, is, is.available());	
						}else
							stat.setNull(i, Types.BLOB);
					}else
						stat.setNull(i, Types.BLOB);
				}else if(value.equals("6")){
					if(paramDomain.getColumnvalue() != null &&(paramDomain.getColumnvalue().toString().length() > 0)) {
						String fieldValue = (String)paramDomain.getColumnvalue();
						if("0".equals(fieldValue)){
							stat.setBoolean(i, true);
						}else{
							stat.setBoolean(i, false);
						}	 
						
					}else
						stat.setNull(i, Types.BIT);
				}
				
				i++;
			}
		} catch (NumberFormatException e) {
			_log.error("在绑定参数变量时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		} catch (SQLException e) {
			_log.error("在绑定参数变量时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		} catch (IOException e) {
			_log.error("在绑定参数变量时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		} catch(Exception ex) {
			_log.error("在绑定参数变量时发生错误，错误信息为：" + ex.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, ex, "err.system.msg.dboperator");
		}
	}
	
	protected String getColumnType(String tableName, String column) {
		String key = "TABLE_COLUNM_TYPE_" + tableName;
		String columnType = "1";
		if(CACHED_TABLE_MAP.containsKey(key)) {
			Map curMap = (Map)CACHED_TABLE_MAP.get(key);
			if(curMap.containsKey(column))
				columnType = curMap.get(column).toString();
		} else {
			TableInfoDomain tableInfoDomain = getColumnInfo(tableName);
			List<ColumnDomain> columnList = tableInfoDomain.getColumnList();
			Map curMap = new HashMap();
			for(int i=0; i<columnList.size(); i++) {
				ColumnDomain columnDomain = (ColumnDomain)columnList.get(i);
				if(columnDomain.getColumnType().equals("clob"))
					curMap.put(columnDomain.getColumnName(), "2");
				else if(columnDomain.getColumnType().equals("blob"))
					curMap.put(columnDomain.getColumnName(), "3");
				else if(columnDomain.getColumnType().equals("num_scale"))
					curMap.put(columnDomain.getColumnName(), "9");
				else
					curMap.put(columnDomain.getColumnName(), "1");
			}
			CACHED_TABLE_MAP.put(key, curMap);
			if(curMap.containsKey(column))
				columnType = curMap.get(column).toString();
		}
		
		return columnType;		
	}
	
	protected boolean isImageColumn(String tableName, String column){
		boolean flag = false;
		String columnType = getColumnType(tableName, column);
		if(columnType.equals("3"))
			flag = true;
		return flag;
	}
	
	protected boolean isNumScaleColumn(String tableName, String column) {
		boolean flag = false;
		String columnType = getColumnType(tableName, column);
		if(columnType.equals("9"))
			flag = true;
		
		return flag;
	}
	
	private String getCountSql(String sql) {
		String retSql = "SELECT COUNT(*) AS sumnum ";
		if((sql.indexOf(" group by") > 0) || (sql.indexOf(" distinct ") > 0)) {
			retSql += " from (" + sql + ") tems";
		}else {
			int index =sql.indexOf(" from ");
			if(index > 0)
				retSql += sql.substring(index);
		}
		
		return retSql;
	}
	
	private String getPagedSql(String sql, String order, List<ParamDomain> queryList, int page, int pageSize) {
		if(page < 1)
			page = 1;
		StringBuffer str = new StringBuffer();
		ParamDomain paramDomain = new ParamDomain();
		paramDomain.setColumntype("integer");
		paramDomain.setColumnvalue("" + (pageSize*(page-1)));
		queryList.add(paramDomain);
		paramDomain = new ParamDomain();
		paramDomain.setColumntype("integer");
		paramDomain.setColumnvalue("" + pageSize);
		queryList.add(paramDomain);
		order = order.toLowerCase();
		if((order.length() > 0) && (order.indexOf(".") < 0) && (order.indexOf(" id ") < 0) && (order.indexOf(",id ") < 0))
			order += ",id asc";		
		str.append("select cotem.* from (").append(sql)
			.append(" ").append(order).append(")cotem limit ?,?");
	
		return str.toString();
	}
}
